

/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 00 FOLDER SETTINGS
** Definition of Start Point
global 		path 	""								


** Input data folder
global bhc_in 		"$path/0 Data/BHCY9C/Input"
global crsp_in		"$path/0 Data/CRSP"
global fred_in		"$path/0 Data/Fred"

** Output data folder 
global bhc_out 		"$path/0 Data/BHCY9C/Output"

** Processed data folder 
global processed 	"$path/0 Data/_Processed"


/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 01 Create a .txt file for CRSP Return download

{
use "$processed/BHC_CRSP_Matching_Table (v2023)",clear
* Note: BHC_CRSP_Matching_Table last updated on 6 Oct 2023
** Identify unique permcos
* Note: there are duplicates with which we will deal later when adding the BHC data
keep permco
duplicates drop

compress
export delimited using "$crsp_in/BHC_Permco_list (v 2023).txt", replace novarnames


**NOTE: We use this .txt list to download the links to CRSP securities daily from WRDS
**		LINK: CRSP / Annual Update / CRSP/Compustat Mergerd / CRSP/Compustat Mergerd Database-Linking Table
** 		Note: Download on lpermco !!
**--> This includes all banks in the matching list i.e. also commercial banks etc. 
**	  These returns are downloaded and prepared as well for a possible later use.
}

** Note: updated on 18 March 2023


/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 02 Prepare CRSP/Compustat linking table

{
use "$crsp_in/US_Bank_CRSP_Compustat_Link (v 2023)", clear

** Reduce to gvkey for CRSP Return download
* Note: duplicates and infos of the link will be processed later
renvars *, lower
keep gvkey 
duplicates drop

compress
export delimited using "$crsp_in/BHC_gvkey_list (v 2023).txt", replace novarnames

**NOTE: We use this .txt list to download the returns from CRSP securities daily from WRDS
**		LINK: Compustat - Capital IQ / Compustat / North America - Daily / Compustat Daily Updated - Security Daily
**		Variables to download: gvkey iid prccd ajexdi cshoc trfd datadate conm sic

}

** Note: updated on 7 October 2023

/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 03 Prepare CRSP/Compustat linking table



****************************************************************************
************* A) Prepare BHC to PERMCO Table *******************************

{
use "$processed/BHC_CRSP_Matching_Table",clear

** Remove duplicate causing problems (different entity but same permco match dates)
** Westbury Bancorp Inc
 drop if entity == 524971
 drop if entity == 4037349 // Cadence Bancorporation (shorter match end date)
 drop if entity == 2033226 // South Plains Financial Inc; not clear why this entity over 3382332 (exact same data inputs)
 
** Drop strings to reduce size
drop name inst

** Replace match end if is equal to 31/09/2022
replace match_end = mdy(03,17,2023) if match_end == mdy(09,30,2021)

** Create a full sample of faily information for matching
egen double bank_id = group(entity permco)
xtset bank_id match_end
tsfill, full

rename match_end date
gen match_end = date if match_start != .
	format match_end %td
	
sort bank_id date	
bysort bank_id: carryforward entity permco match_start match_end, replace

gsort bank_id -date
bysort bank_id: carryforward entity permco match_start match_end, replace
sort bank_id date


** Remove dates outside the matching period
drop if (date < match_start | date > match_end)

** Remove duplicates arising from overlapping match end and new match start
duplicates tag permco date,gen(tag)

tab tag
tab entity if  tag > 0
drop if tag > 0
drop tag  bank_id

compress
save "$crsp_in/BHC_Permco_Daily_Match (v 2022)", replace

}

** Note: updated on 18 March 2023

****************************************************************************
************* B) Prepare CRSP to PERMCO Table merge with A *****************

{
** Load Table with GVKEY-PERMCO Match
use "$crsp_in/US_Bank_CRSP_Compustat_Link (v 2022)", clear

renvars *, lower


* Rename for matching
rename lpermco permco
rename liid iid

** Generate unique CRSP id
egen double crsp_id = group(gvkey iid permco)

* Destring gvkey 
destring gvkey, replace

keep gvkey linkprim iid linktype permco linkdt linkenddt crsp_id id

gen linkend_missing = (linkenddt == .e)
	replace linkenddt = mdy(03,17,2023) if linkenddt == .e

duplicates tag permco linkenddt,gen(tag)
drop if tag > 0 & linkprim == "J"
drop tag


xtset crsp_id linkenddt
tsfill, full


rename linkenddt date
gen linkenddt = date if linkdt != .
	format linkenddt %td
	
sort crsp_id date	
bysort crsp_id: carryforward gvkey permco id linkdt linkenddt iid linkend_missing, replace

gsort crsp_id -date
bysort crsp_id: carryforward gvkey permco id linkdt linkenddt iid linkend_missing, replace
sort crsp_id date


** Remove dates outside the matching period
drop if (date < linkdt | date > linkenddt)

drop linkprim linktype


** Drop if not primary issuer
duplicates tag permco date,gen(tag)

tab tag
tab gvkey if tag > 0 // two gvkeys: 4710, 33999

drop if tag >0 
drop tag



** Merge with BHC Permco Daily match
merge 1:1 permco date using "$crsp_in/BHC_Permco_Daily_Match (v 2022)"
drop if _merge == 1


** Remove dates outside the matching period
drop if (date < match_start | date > match_end)
drop if (date < linkdt | date > linkenddt)


sort permco date	
bysort permco: carryforward gvkey iid linkdt id crsp_id linkend_missing linkenddt, replace

gsort permco -date
bysort permco: carryforward gvkey iid linkdt id crsp_id linkend_missing linkenddt, replace
sort permco date

** Reduce dataset
drop if gvkey == .
drop _merge id crsp_id

order gvkey iid permco entity date linkdt linkenddt linkend_missing


compress
save "$crsp_in/BHC_GVKEY_Match_Daily (v 2022)", replace
}


** Note: last updated 18 March 2023

/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 04 Load Fred one-month/three-month treasury bill rates

{
** Import Excel file (from: https://fred.stlouisfed.org/series/DGS1MO)
import excel using "$fred_in/DGS1MO.xls", firstrow  sheet("FRED Graph") cellrange(A11:B5655) clear

** Remove zeros that happen in data on random days (they are empty on fed webpage; to not loose them I carry the previous day forward)
sort obs
replace DGS = . if DGS == 0
carryforward DGS, replace

** Rename variables
rename		observation_date 	datadate
rename		DGS1MO 				r_one_month_treasury

** Adjust rate
*	(i) 	Format from percentage
format		r_one_month_treasury %10.0g
replace 	r_one_month_treasury = r_one_month_treasury / 100 
*	(ii) 	Frequency to daily (360 days vs 250 trading days??!)
replace 	r_one_month_treasury = r_one_month_treasury / 360

** Save 
compress
save "$processed/Fred_one_month_treasury_rate_daily", replace
}



{
** Import Excel file (from: https://fred.stlouisfed.org/series/DGS1MO)
import excel using "$fred_in/DGS3MO.xls", firstrow  sheet("FRED Graph") cellrange(A11:B10850) clear

** Remove zeros that happen in data on random days (they are empty on fed webpage; to not loose them I carry the previous day forward)
sort obs
replace DGS = . if DGS == 0
carryforward DGS, replace

** Rename variables
rename		observation_date 	datadate
rename		DGS3MO 				r_three_month_treasury

** Adjust rate
*	(i) 	Format from percentage
format		r_three_month_treasury %10.0g
replace 	r_three_month_treasury = r_three_month_treasury / 100 
*	(ii) 	Frequency to daily (360 days vs 250 trading days??!)
replace 	r_three_month_treasury = r_three_month_treasury / 360

** Save 
compress
save "$processed/Fred_three_month_treasury_rate_daily", replace
}




* Note: updated on 18 March 2023

/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 05  Add Matching Table to CRSP Return data and comput log returns

{

use "$crsp_in/US_Bank_returns_daily_1995_2022.dta", clear


** Rename variables for merging
rename 	datadate date
rename GVKEY gvkey
destring gvkey, replace

** Add gvkey-bhc match
merge 	1:1 gvkey iid date using "$crsp_in/BHC_GVKEY_Match_Daily (v 2022)"


** Drop obs with no price or entity information
drop 	if (prccd == . | entity == .)
drop	if _merge == 2
drop 	_merge


** Compute log returns
egen 	double bank_id = group(entity permco)
xtset 	bank_id date

* Adjust prices and shares outstandign for stock splits etc. (using AJEXDI)
gen			prc_adj				= prccd / ajexdi 
gen			cshoc_adj			= cshoc * ajexdi


*(i) simple return
sort bank_id date
by bank_id:	generate	simple_return_total	= (prc_adj * trfd) / (prc_adj[_n-1] * trfd[_n-1]) - 1
by bank_id: replace		simple_return_total	= prc_adj / prc_adj[_n-1] - 1  

*(ii) log return
by bank_id:	gen 		log_return_total 	= log(prc_adj * trfd) 	- log(prc_adj[_n-1] * trfd[_n-1])
by bank_id:	replace 	log_return_total 	= log(prc_adj) 			- log(prc_adj[_n-1]) 			if log_return_total == .


* Compute marketcap
gen			market_cap			= prc_adj * cshoc_adj


* Add one-month treasury bill rate to compute excess returns 
rename 	date datadate
merge	 m:1 datadate using "$processed/Fred_one_month_treasury_rate_daily"

* Note: 1m treasuries are only available starting 1.1.2001; if earlier data is required, the 3m Treasuries go back to 1980


* carry forward as fed rates are not upated yet (should not make a big difference)
sort bank_id datadate
by bank_id: carryforward r_one_month_treasury if year(datadate) == 2020, replace

drop	if r_one_month_treasury == .
drop 	_merge
rename	datadate date

* Carryforward if rf is missing on a certiain day (0nly in 0.76% of cases missing)
sort gvkey iid date
by gvkey iid: carryforward r_one_month_treasury, replace

* Compute excess return (as in Fahlenbach/Rageth/Stulz, 2020, p.36)
gen		excess_return			= log(1 + simple_return_total - r_one_month_treasury)



** Keep only relevant variables
keep 	gvkey date conm sic permco entity linkdt linkenddt linkend_missing match_start match_end prc_adj log_return cshoc_adj excess_return simple_return_total market_cap

order 	gvkey permco entity date conm sic excess_return simple_return_total  log_return prc_adj cshoc_adj
sort 	gvkey date

drop 	if gvkey == .

** Save
compress
save "$processed/US_Bank_log_returns_daily_1995-2022 (v 2022)", replace // downloaded and saved 10 March 2022

}


** Note: last updated 18 March 2023
